From 7462c48eddea6db464876a4a847f1e9c8f9a2c22 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 22 Jul 2026 21:06:14 +0000 Subject: [PATCH] Adjust PANTGRUNTS_VIEW for the new WATCHES.Type values The new WATCHES design (A-Record type, B-Record type, etc.) means that there is no longer a hierarchy of WATCHES.Type values that pantgrunt data must conform to. We no longer require a pantgrunt to be attached to a B record watch, if one exists, and if not then a pantgrunt watch. This means the PANTGRUNTS_VIEW code can delegate all the work to the DYADS view, and we can get rid of a lot of code. (If the PANTGRUNTS_VIEW is ever to raise an error, the deleted _show_row() macro may prove useful to retrieve from the deleted code.) --- .../lib/triggers/create/pantgrunts_view.m4 | 382 +----------------- doc/src/views/pantgrunts_view.m4 | 67 +-- 2 files changed, 45 insertions(+), 404 deletions(-) diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 index 8982f65..72d6742 100644 --- a/db/schemas/lib/triggers/create/pantgrunts_view.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -26,56 +26,6 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`macros.m4')dnl -dnl Plpgsql fragment for use in errors to show content of NEW row -dnl -dnl Syntax: _show_row() -dnl -dnl Remarks: -dnl This makes everything more readable. -dnl -changequote({,}) -define({_show_row}, {'Value (Date) = (' - || textualize(`NEW.date') - || '), Value (AnimID) = (' - || textualize(`NEW.animid') - || '), Value (Time) = (' - || textualize(`NEW.time') - || '), Value (Actor) = (' - || textualize(`NEW.actor') - || '), Value (Recipient) = (' - || textualize(`NEW.recipient') - || '), Value (MultiActors) = (' - || textualize(`NEW.multiactors') - || '), Value (MultiRecipients) = (' - || textualize(`NEW.multirecipients') - || '), Value (TwoSided) = (' - || textualize(`NEW.TwoSided') - || '), Value (CommID) = (' - || textualize(`NEW.commid') - || '), Value (PG_CommID) = (' - || textualize(`NEW.pg_commid') - || '), Value (Source) = (' - || textualize(`NEW.source') - || '), Value (EnteredBy) = (' - || textualize(`NEW.enteredby') - || '), Value (Notes) = (' - || textualize(`NEW.notes') - || '), Value (Event_Notes) = (' - || textualize(`NEW.event_notes') - || '), Value (Type) = (' - || textualize(`NEW.type') - || '), Key (WID) = (' - || textualize(`NEW.wid') - || '), Key (EID) = (' - || textualize(`NEW.eid') - || '), Key (Actor_PID) = (' - || textualize(`NEW.actor_pid') - || '), Key (Recipient_PID) = (' - || textualize(`NEW.recipient_pid') - || ')'dnl -}) -changequote(`,') - RAISE INFO 'pantgrunts_view_insert_func'; CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () RETURNS trigger @@ -98,7 +48,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () target_actor_pid roles.pid%TYPE; target_recipient_pid roles.pid%TYPE; - target_twosided BOOLEAN; + target_style TEXT; BEGIN -- Function for pantgrunts_view instead of insert trigger @@ -107,321 +57,9 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () `The Meme Factory, Inc., www.karlpinc.com') -- -- Remarks: - -- Do not enforce any conditions on the ID values. The place - -- to do that is in the triggers on the tables, so the rules are - -- consistent no matter the interface used to change table content. - - -- - -- The reason why we go through all this searching existing db - -- content, in various ways, is to get a WATCHES.Type value - -- to send to the DYADS view. We want to do this in a way - -- that's consistent with the way DYADS searches. - -- - -- In this particular case the Start and Stop values are guarenteed - -- to be identical, and the default watch type is 'sdb_brec'. - -- And Certainty is guarenteed to be sdb_identity_certain. So this - -- code is aware of that and does not even mention Certainty to the - -- user. - -- - -- If the are more views written that model off this view, the - -- best approach is to probably write a general-purpose function - -- that discovers the WATCHES.Type and have each view call that - -- function. This would make writing such views trivial: call the - -- function and insert into DYADS. Unfortunately, writing such - -- a function is currently out-of-scope. - - -- - -- Initial validation of data supplied - -- - IF NEW.eid IS NULL THEN - - -- Use other data values to find the EID to which to relate the - -- new ROLES rows. - - -- Time must be supplied - to lookup EVENTS - IF NEW.time IS NULL THEN - RAISE EXCEPTION data_exception USING - MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The supplied Time value may not be NULL' - || ' when EID is NULL' - || ': ' - || _show_row(); - END IF; - target_time := NEW.time; - - IF NEW.wid IS NULL THEN - -- Use Date, AnimID, and Time to query OBS. - - -- Date must be supplied - to lookup WATCHES - IF NEW.date IS NULL THEN - RAISE EXCEPTION data_exception USING - MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The supplied Date value may not be NULL' - || ' when WID is NULL' - || ': ' - || _show_row(); - END IF; - - -- AnimID must be supplied - to lookup WATCHES - IF NEW.animid IS NULL THEN - RAISE EXCEPTION data_exception USING - MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The supplied AnimID value may not be NULL' - || ' when WID is NULL' - || ': ' - || _show_row(); - END IF; - - SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid - , obs.notes, obs.event_notes - INTO target_wid, target_date, target_animid, target_type, target_commid - , target_notes, target_event_notes - FROM obs - WHERE obs.animid = NEW.animid - AND obs.date = NEW.date - AND obs.behavior = 'sdb_pg_event' - AND obs.start = NEW.time - AND obs.stop = NEW.time; - --- Validation is already done by OBS. Leaving this comment on --- the chance there's some utility if and when this code --- is abstracted into a function. --- (CAUTION: textualiz`'e() and _show_ro`'w() macros are turned off.) --- --- IF FOUND THEN --- -- Validate supplied values against db values --- IF (NEW.type IS NOT NULL --- AND NEW.type <> target_type) --- OR (NEW.commid IS NOT NULL --- AND NEW.commid <> target_commid) --- OR (NEW.notes IS NOT NULL --- AND NEW.notes <> target_notes) --- OR (NEW.event_notes IS NOT NULL --- AND NEW.event_notes <> target_event_notes) THEN --- RAISE EXCEPTION data_exception USING --- MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' --- , DETAIL = 'The values in the database for the OBS' --- ||' row with (OBS.WID) = (' --- || textuali`'ze(`NEW.wid') --- || ') and (OBS.Behavior = (' --- || 'sdb_pg_event' --- || ') and (OBS.Start = (' --- || textuali`'ze(`NEW.time') --- || ') and (OBS.Stop = (' --- || textuali`'ze(`NEW.time') --- || '), which are:' --- || ' Key (WID) = (' --- || target_wid --- || '), Value (Date) = (' --- || target_date --- || '), Value (AnimID) = (' --- || target_animid --- || '), Value (Type) = (' --- || target_type --- || '), Value (CommID) = (' --- || target_commid --- || '), Key (EID) = (' --- || target_eid --- || '), Value (Notes) = (' --- || target_notes --- || '), Value (Event_Notes) = (' --- || target_event_notes --- || ') do not match the values supplied to the INSERT' --- || ': ' --- || _show_ro`'w(); --- END IF; --- ELSE - IF NOT FOUND THEN - -- No such OBS row, find a target_type - IF NEW.type IS NULL THEN - SELECT watches.type - INTO target_type - FROM watches - WHERE watches.animid = NEW.animid - AND watches.date = NEW.date - AND (watches.type = 'sdb_brec' - OR watches.type = 'sdb_other_watch') - -- Use sdb_brec as the default when there's both. - ORDER BY watches.type = 'sdb_brec' DESC; - - IF NOT FOUND THEN - -- When a WATCHES row must be created then there must - -- not be an existing follow, so use the sdb_other_watch Type. - target_type := 'sdb_other_watch'; - END IF; - ELSE - -- Use the user's chosen type and let the errors fly as they may. - target_type := NEW.type; - END IF; - END IF; - ELSE -- NEW.wid IS NOT NULL - target_wid := NEW.wid; - - -- Use WID and Time to query OBS - SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid - , obs.eid - , obs.notes, obs.event_notes - INTO target_wid, target_date, target_animid, target_type, target_commid - , target_eid - , target_notes, target_event_notes - FROM obs - WHERE obs.wid = NEW.wid - AND obs.behavior = 'sdb_pg_event' - AND obs.start = NEW.time - AND obs.stop = NEW.time; - --- Validation is already done by OBS. Leaving this comment on --- the chance there's some utility if and when this code --- is abstracted into a function. --- (CAUTION: textualiz`'e() and _show_ro`'w() macros are turned off.) --- --- IF FOUND THEN --- -- Validate supplied values against db values --- IF (NEW.date IS NOT NULL --- AND NEW.date <> target_date) --- OR (NEW.animid IS NOT NULL --- AND NEW.animid <> target_animid) --- OR (NEW.type IS NOT NULL --- AND NEW.type <> target_type) --- OR (NEW.commid IS NOT NULL --- AND NEW.commid <> target_commid) --- OR (NEW.notes IS NOT NULL --- AND NEW.notes <> target_notes) --- OR (NEW.event_notes IS NOT NULL --- AND NEW.event_notes <> target_event_notes) THEN --- RAISE EXCEPTION data_exception USING --- MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' --- , DETAIL = 'The values in the database for the OBS' --- ||' row with (OBS.WID) = (' --- || NEW.wid --- || ') and (OBS.Behavior = (' --- || 'sdb_pg_event' --- || ') and (OBS.Start = (' --- || textuali`'ze(`NEW.time') --- || ') and (OBS.Stop = (' --- || textuali`'ze(`NEW.time') --- || '), which are:' --- || ' Key (WID) = (' --- || target_wid --- || '), Value (Date) = (' --- || target_date --- || '), Value (AnimID) = (' --- || target_animid --- || '), Value (Type) = (' --- || target_type --- || '), Value (CommID) = (' --- || target_commid --- || '), Key (EID) = (' --- || target_eid --- || '), Value (Notes) = (' --- || target_notes --- || '), Value (Event_Notes) = (' --- || target_event_notes --- || ') do not match the values supplied to the INSERT' --- || ': ' --- || _show_ro`'w(); --- END IF; --- ELSE - IF NOT FOUND THEN - -- No such OBS row, find a target_type - IF NEW.type IS NULL THEN - SELECT watches.type - INTO target_type - FROM watches - WHERE watches.wid = NEW.wid; - - IF NOT FOUND THEN - -- When a WATCHES row must be created then there must - -- not be an existing follow, so use the sdb_other_watch Type. - target_type := 'sdb_other_watch'; - END IF; - ELSE - -- Use the user's chosen type and let the errors fly as they may. - target_type := NEW.type; - END IF; - END IF; - END IF; - - ELSE -- NEW.EID IS NOT NULL - target_eid := NEW.eid; - - -- Use EID to lookup OBS - SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid - , obs.start - , obs.notes, obs.event_notes - INTO target_wid, target_date, target_animid, target_type, target_commid - , target_time - , target_notes, target_event_notes - FROM obs - WHERE obs.eid = NEW.eid - AND obs.behavior = 'sdb_pg_event'; - - IF FOUND THEN - -- Validate supplied values against db values - IF (NEW.wid IS NOT NULL - AND NEW.wid <> target_wid) - OR (NEW.date IS NOT NULL - AND NEW.date <> target_date) - OR (NEW.animid IS NOT NULL - AND NEW.animid <> target_animid) - OR (NEW.type IS NOT NULL - AND NEW.type <> 'sdb_brec' - AND NEW.type <> 'sdb_other_watch') - OR (NEW.commid IS NOT NULL - AND NEW.commid <> target_commid) - OR (NEW.time IS NOT NULL - AND NEW.time <> target_time) - OR (NEW.notes IS NOT NULL - AND NEW.notes <> target_notes) - OR (NEW.event_notes IS NOT NULL - AND NEW.event_notes <> target_event_notes) THEN - RAISE EXCEPTION data_exception USING - MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The values in the database for the OBS' - || ' row with (OBS.EID) = (' - || NEW.eid - || '), which are:' - || ' Key (WID) = (' - || target_wid - || '), Value (Date) = (' - || target_date - || '), Value (AnimID) = (' - || target_animid - || '), Value (Type) = (' - || target_type - || '), Value (CommID) = (' - || target_commid - || '), Key (EID) = (' - || target_eid - || '), Value (Behavior) = (' - || 'sdb_pg_event' - || '), Value (Start) = (' - || target_time - || '), Value (Stop) = (' - || target_time - || '), Value (Notes) = (' - || target_notes - || '), Value (Event_Notes) = (' - || target_event_notes - || ') do not match the values supplied to the INSERT' - || ': ' - || _show_row(); - END IF; - ELSE - RAISE EXCEPTION data_exception USING - MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The EVENTS row with (EVENTS.EID) = (' - || NEW.eid - || ') and (EVENTS.Behavior) = (' - || 'sdb_pg_event' - || ') does not exist' - || ': ' - || _show_row(); - END IF; - END IF; - - -- The requirement that there be non-NULL data in the rest of the columns - -- is checked by the underlying tables. + -- The error reporting would be improved if we did checking here + -- instead of letting DYADS and the underlying tables do the checking. + -- But that's too much work. -- -- Insert the DYADS row. @@ -432,30 +70,30 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () , certainty , actor_pid , actor , recipient_pid , recipient - , twosided + , style , notes , event_notes) - VALUES ( NEW.wid, NEW.date, NEW.animid, target_type, NEW.commid + VALUES ( NEW.wid, NEW.date, NEW.animid, NEW.type, NEW.commid , NEW.eid, 'sdb_pg_event', NEW.time, NEW.time , 'sdb_identity_certain' , NEW.actor_pid, NEW.actor , NEW.recipient_pid, NEW.recipient - , NEW.twosided + , NEW.style , NEW.notes, NEW.event_notes) RETURNING wid, date, animid, type, commid , eid, start , actor_pid, actor , recipient_pid, recipient - , twosided + , style , notes, event_notes) SELECT ins.wid , ins.date , ins.animid , ins.type , ins.commid , ins.eid , ins.start , ins.actor_pid, ins.recipient_pid - , ins.twosided + , ins.style , ins.notes , ins.event_notes INTO target_wid, target_date, target_animid, target_type, target_commid , target_eid, target_time , target_actor_pid, target_recipient_pid - , target_twosided + , target_style , target_notes, target_event_notes FROM ins; diff --git a/doc/src/views/pantgrunts_view.m4 b/doc/src/views/pantgrunts_view.m4 index ad42abe..5299d72 100644 --- a/doc/src/views/pantgrunts_view.m4 +++ b/doc/src/views/pantgrunts_view.m4 @@ -34,9 +34,12 @@ PANTGRUNTS_VIEW The view puts the individual performing the pantgrunt and the individual receiving the pantgrunt in separate columns. -There is a flag to indicate whether the pantgrunting is mutual; when -|true| ,there is not meaning to the placement of the interacting -individuals into these two columns. + +The Style column indicates whether the pantgrunting was directed or mutual. +The values of Style are those of the |DYADS|.Style column, although given +the nature of the pantgrunt data the only Style values that can occur +are ``sdb_directed_style`` and ``sdb_mutual_style``. +The documentation of the |DYADS| view contains more detail. .. _PANTGRUNTS_VIEW_Definition: @@ -92,8 +95,11 @@ Columns of the PANTGRUNTS_VIEW View +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | MultiRecipients | |PANTGRUNTS|.\ |PANTGRUNTS.MultiRecipients| | Boolean, |true| when there were mulitple recipients of a pantgrunt | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | TwoSided | An expression based on |ROLES|.\ |ROLES.Role| | Boolean, |true| when both the Actor and the Recipient were | - | | | pantgrunting to each other | + | Style | An expression based on |ROLES|.\ |ROLES.Role| | How the Actor and the Recipient interacted. One of: | + | | | |spx_directed_style| -- The Actor acted upon the Recipient | + | | | | + | | | |spx_mutual_style| -- The Actor and the Recipient acted upon each | + | | | other | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated either with the follow or with | | | | the ad-hoc recording of the pantgrunt | @@ -131,47 +137,44 @@ Columns of the PANTGRUNTS_VIEW View Operations Allowed `````````````````` +Internally, to manipulate the content of the the |EVENTS|, |WATCHES|, +and |ROLES| tables, operations done on the PANTGRUNTS_VIEW do the +equalivant operation on |DYADS|. +This means the rules regarding what data must be supplied when +INSERTing into |DYADS|, and when |DYADS| re-uses existing rows or +creates new rows, also apply to PANTGRUNTS_VIEW. + +This also means errors in the data supplied to the PANTGRUNTS_VIEW are +reported by the |DYADS| view, as well as by the underlying tables. +This impacts the text of the error messages presented to the user. +In particular, the PANTGRUNTS_VIEW.Time column is used to supply the +|DYADS|.Start and |DYADS|.Stop values. +So errors involving the PANTGRUNTS_VIEW.Time column will be reported +as a problem with either the |DYADS|.Start or |DYADS|.Stop column. + INSERT - INSERTing a row into PANTGRUNTS_VIEW inserts a row into + INSERTing a row into the PANTGRUNTS_VIEW inserts a row into |PANTGRUNTS| and two rows into into |ROLES|. One row may be inserted into |EVENTS|, and one may be inserted into |WATCHES|. - The columns EID, WID, AnimID, Date, and Time may all be used, in the - various combinations described below, to relate new |PANTGRUNTS| - and |ROLES| rows to existing |EVENTS| rows, or to create new - |EVENTS| and |WATCHES| rows when they do not already exist. - - If an EID is supplied, that is all that is required. - The identified |EVENTS| row must already exist. - - If an EID is not supplied, the columns WID, Date, and Time are used - to query |OBS| to discover if a matching |EVENTS| row exists or if - there is no match and a new one must be inserted. - The Time column must be supplied in this case. - - If a WID is supplied it is used, along with Time, to match against - database content. - If a WID is not supplied, the AnimID and Date columns must - be supplied. - Then they are used, along with Time, to match against database - content. + The columns EID, WID, AnimID, Date, and Time may all be used, in + the various combinations described in the |DYADS| documentation, to + relate new |PANTGRUNTS| and |ROLES| rows to existing |EVENTS| rows, + or to create new |EVENTS| and |WATCHES| rows when they do not + already exist. If an existing |EVENTS| row matches, the new |PANTGRUNTS| row and the new |ROLES| rows are related to the |EVENTS| row so discovered. Otherwise they are related to the newly created |EVENTS| row. - INSERTing into PANTGRUNTS_VIEW inserts a row into |DYADS|. - This means the rules regarding what data must be supplied when - INSERTing into |DYADS|, and when |DYADS| re-uses existing rows or - creates new rows, also apply to PANTGRUNTS_VIEW. - When existing rows are found in the database, all (non-|null|) data values supplied must match the data values that already exist. - The WID, EID, Actor_PID, and Recipient_PID columns do not have - their values inserted into new rows. + As with the |DYADS| view, the WID, EID, Actor_PID, and + Recipient_PID columns do not have their values inserted into new + rows. If non-|null| values are supplied for these columns they must match the values already existing in, or inserted into, the database. -- 2.34.1